''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：alibaba_month_data.py
#日期：2023-11-17
#备注：阿里巴巴国际站月统计数
1. 月L+商机数
CREATE TABLE `alibaba_feedback_list` (
  `requestNo` varchar(20) COMMENT '询价单号唯一', 
  `source` varchar(50) COMMENT '询价源头',
  `appFrom` varchar(50) COMMENT '询价来自',
  `createTime` date COMMENT '询价日期',
  `ownerName` varchar(20) COMMENT '负责人', 
  `productId` varchar(20) COMMENT '对应产品id', 
  `productImage` varchar(200) COMMENT '产品主图', 
  `countryCode` varchar(20) COMMENT '询盘国家简称',
  `countryName` varchar(50) COMMENT '询盘国家全称',
  `name` varchar(50) COMMENT '询盘人',
  `userNewLevel` varchar(20) COMMENT '买家分层L1+',
  `joinYear` int(3) COMMENT '进入阿里巴巴采购年限',
  `subject` varchar(200) COMMENT '商机来源',
  PRIMARY KEY (`requestNo`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='阿里巴巴商机数统计';
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

from file_deal import replace_txt
from pycacho.cachobase.deal_time import stamp_to_date
from pacong.stock.stock_config import db,cursor
from pycacho.cachobase.logger import Logger
logger = Logger('alibaba_month_data').get_log()

# 询盘列表
def get_feedback_list():
    file = 'F:\\alibaba\json\\feedbackSubjectList.txt'
    feedback_list = replace_txt(file)
    for feedback in feedback_list:
        for item in feedback:
            requestNo =  item['requestNo']  # 询价单号唯一
            source = item['source']  # 询价源头
            if source in ["RFQ"]:
                appFrom = 'RFQ'
                subject = "RFQ"  # 商机分类
            elif source in ['main_en_detail']:
                appFrom = ''
                subject = item['subject']  # 商机分类
            else:
                appFrom = item['appFrom']  # 询价来自
                subject = item['subject']  # 商机分类
            createTime = stamp_to_date(item['createTime'])  # 创建时间
            ownerName = item['ownerName']  # 负责人
            if source in ["CONTACT_MKT_IMOpportunity",'main_app_esite-cs']:
                productId = ''
                productImage = ''
            elif source in ["RFQ"]:
                productId = ''
                productImage = item['productInfo'][0]['imageUrl']
            else:
                productId = item['productInfo'][0]['productId']  # 产品id
                productImage = item['productInfo'][0]['imageUrl']  # 产品图片
            countryCode = item['sender']['countryCode'] #
            countryName = item['sender']['countryName'] #
            name = item['sender']['name'] #
            print(requestNo,source,appFrom,createTime,ownerName,productId,productImage,
                  countryCode,countryName,name,subject)

# 询盘买家分层信息
def get_feedback_info():
    file = 'F:\\alibaba\json\\feedbackSubjectInfo.txt'
    feedback_info = replace_txt(file)
    for feedback in feedback_info:
        sender = feedback_info[feedback]['sender']
        if len(sender)>3:
            userNewLevel = sender['userNewLevel']
            joinYear = sender['joinYear']
        else:
            userNewLevel = ''
            joinYear = sender['joinYear']
        #print(feedback,userNewLevel,joinYear)
        update_feedback_list(userNewLevel, joinYear,feedback)


def insert_feedback_list(requestNo,source,appFrom,createTime,ownerName,productId,productImage,countryCode,countryName,name,subject):
    sql = "insert into alibaba_feedback_list (requestNo,source,appFrom,createTime,ownerName,productId,productImage,countryCode,countryName,name,userNewLevel,joinYear,subject) " \
          "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','',0,'%s');" \
          % (requestNo,source,appFrom,createTime,ownerName,productId,productImage,countryCode,countryName,name,subject)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('阿里巴巴商机%s统计插入成功！', requestNo)

def update_feedback_list(userNewLevel,joinYear,requestNo):
    sql = "update alibaba_feedback_list set userNewLevel='%s', joinYear='%s' where requestNo='%s'" % (userNewLevel,joinYear,requestNo)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('更新阿里巴巴商机数 %s成功！', requestNo)

if __name__=='__main__':
    get_feedback_list()
    #get_feedback_info()